package com.xinyirun.scm.core.system.mapper.business.schedule;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xinyirun.scm.bean.entity.business.schedule.BScheduleEntity;
import com.xinyirun.scm.bean.system.vo.business.schedule.BScheduleSumVo;
import com.xinyirun.scm.bean.system.vo.business.schedule.BScheduleVo;
import com.xinyirun.scm.bean.system.vo.excel.schedule.BScheduleExcelVo;
import com.xinyirun.scm.common.constant.DictConstant;
import com.xinyirun.scm.common.constant.SystemConstants;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author htt
 * @since 2021-09-23
 */
@Repository
public interface BScheduleMapper extends BaseMapper<BScheduleEntity> {

    String common_select = "  "
            + "     SELECT                                                                                                                              "
            + "            @row_num:= @row_num+ 1 as excel_no,                                                                                          "
            + "            t.id,                                                                                                                        "
            + "            t.type,                                                                                                                      "
            + "            t.code,                                                                                                                      "
            + "            t.status,                                                                                                                    "
            + "            t.out_type,                                                                                                                  "
            + "            t.in_type,                                                                                                                   "
            + "            t.in_plan_code,                                                                                                              "
            + "            t.in_plan_detail_code,                                                                                                       "
            + "            t.in_plan_detail_id,                                                                                                         "
            + "            t.out_plan_code,                                                                                                             "
            + "            t.out_plan_detail_code,                                                                                                      "
            + "            t.out_plan_detail_id,                                                                                                        "
            + "            t.order_id,                                                                                                                  "
            + "            t.sku_id,                                                                                                                    "
            + "            t.sku_code,                                                                                                                  "
            + "            t.out_warehouse_id,                                                                                                          "
            + "            t.out_location_id,                                                                                                           "
            + "            t.out_bin_id,                                                                                                                "
            + "            t.in_warehouse_id,                                                                                                           "
            + "            t.in_location_id,                                                                                                            "
            + "            t.in_bin_id,                                                                                                                 "
            + "            t.out_schedule_qty,                                                                                                          "
            + "            t.out_operated_qty,                                                                                                          "
            + "            t.out_balance_qty,                                                                                                           "
            + "            t.out_unit_id,                                                                                                               "
            + "            t.in_schedule_qty,                                                                                                           "
            + "            t.in_balance_qty,                                                                                                            "
            + "            t.in_operated_qty,                                                                                                           "
            + "            t.in_unit_id,                                                                                                                "
            + "            t.transport_type,                                                                                                            "
            + "            t.in_consignor_id,                                                                                                           "
            + "            t.in_consignor_code,                                                                                                         "
            + "            t.in_owner_id,                                                                                                               "
            + "            t.in_owner_code,                                                                                                             "
            + "            t.in_warehouse_address,                                                                                                      "
            + "            t.out_consignor_id,                                                                                                          "
            + "            t.out_consignor_code,                                                                                                        "
            + "            t.out_owner_id,                                                                                                              "
            + "            t.out_owner_code,                                                                                                            "
            + "            t.out_warehouse_address,                                                                                                     "
            + "            t.out_rule,                                                                                                                  "
            + "            t.c_time,                                                                                                                    "
            + "            t.u_time,                                                                                                                    "
            + "            t.c_id,                                                                                                                      "
            + "            t.u_id,                                                                                                                      "
            + "            t.dbversion,                                                                                                                 "
            + "            t.in_rule,                                                                                                                   "
            + "            CASE WHEN t.out_operated_qty-t.out_schedule_qty > 0 then t.out_operated_qty-t.out_schedule_qty ELSE 0 END out_over_qty,      "
            + "            CASE WHEN t.in_operated_qty-t.in_schedule_qty > 0 then t.in_operated_qty-t.in_schedule_qty ELSE 0 END in_over_qty,           "
            + "            ifnull(t3.short_name,t3.name) out_warehouse_name,                                                                            "
            + "            t3.name out_warehouse_full_name,                                                                                             "
            + "            t22.label out_warehouse_type_name,                                                                                           "
            + "            ifnull(round(t.out_operated_qty*100/t.out_schedule_qty, 2), 0) out_rate,                                                     "
            + "            ifnull(round(t.in_operated_qty*100/t.in_schedule_qty, 2), 0) in_rate,                                                        "
            + "            t.out_warehouse_address,                                                                                                     "
            + "            ifnull(t4.short_name,t4.name) in_warehouse_name,                                                                             "
            + "            t4.name in_warehouse_full_name,                                                                                              "
            + "            t23.label in_warehouse_type_name,                                                                                            "
            + "            t.in_warehouse_address,                                                                                                      "
            + "            ifnull(t7.contract_no,t.contract_no) contract_no,                                                                            "
            + "            t7.contract_dt,                                                                                                              "
            + "            t7.contract_num,                                                                                                             "
            + "            t7.order_no,                                                                                                                 "
            + "            t7.ship_name,                                                                                                                "
            + "            t7.customer_name order_customer_name,                                                                                        "
            + "            t7.owner_name,                                                                                                               "
            + "            t7.bill_type_name,                                                                                                           "
            + "            case when t7.serial_type = 'b_in_order' then '采购订单' else '销售订单' end as serial_type_name,                                "
            + "            t6.name as goods_name,                                                                                                       "
            + "            t6.pm as pm,                                                                                                                 "
            + "            t6.spec as spec,                                                                                                             "
            + "            t.monitor_count,                                                                                                             "
            + "            t8.label as status_name,                                                                                                     "
            + "            t12.name as in_owner_name,                                                                                                   "
            + "            t13.name as in_consignor_name,                                                                                               "
            + "            t14.name as out_owner_name,                                                                                                  "
            + "            t15.name as out_consignor_name,                                                                                              "
            + "            t1.name as c_name,                                                                                                           "
            + "            t2.name as u_name,                                                                                                           "
            + "            t17.customer_id,                                                                                                             "
            + "            t17.customer_code,                                                                                                           "
            + "            ifnull(t18.short_name, t18.name) customer_name,                                                                              "
            + "            ifnull(t20.in_transit, 0) in_transit,                                                                                        "
            + "            ifnull(t27.return_qty, 0) return_qty,                                                                                        "
            + "            t21.label type_name,                                                                                                         "
            + "            ifnull(t17.waybill_contract_no, t24.carriage_contract_code) waybill_contract_no,                                             "
            + "            t24.order_no carriage_order_no,                                                                                               "
            + "            t25.contract_no in_contract_no,                                                                                               "
            + "            t26.contract_no out_contract_no,                                                                                               "
            + "            t6.goods_code goods_code                                                                                               "
            + "       FROM                                                                                                                              "
            + "  	       b_schedule t                                                                                                                 "
            + "  LEFT JOIN m_staff t1 ON t.c_id = t1.id                                                                                                 "
            + "  LEFT JOIN m_staff t2 ON t.u_id = t2.id                                                                                                 "
            + "  LEFT JOIN m_warehouse t3 ON t.out_warehouse_id = t3.id                                                                                 "
            + "  LEFT JOIN m_warehouse t4 ON t.in_warehouse_id = t4.id                                                                                  "
            + "  LEFT JOIN b_out_plan_detail t5 ON t.out_plan_detail_id = t5.id                                                                          "
            + "  LEFT JOIN m_goods_spec t6 ON t.sku_id = t6.id                                                                                          "
            + "  LEFT JOIN (                                                                                                                            "
            + "		SELECT                                                                                                                              "
            + "			  t1.id,                                                                                                                        "
            + "           t2.contract_no,                                                                                                               "
            + "           t2.contract_num,                                                                                                              "
            + "		      t2.order_no,                                                                                                                  "
            + "		      t2.contract_dt,                                                                                                               "
            + "		      t2.ship_name,                                                                                                                 "
            + "		      ifnull(t3.short_name,t3.name) customer_name,                                                                                  "
            + "		      t1.serial_type,                                                                                                               "
            + "		      t4.label bill_type_name,                                                                                                      "
            + "		      ifnull(t5.short_name,t5.name) owner_name                                                                                      "
            + "		FROM                                                                                                                                "
            + "			b_order t1                                                                                                                      "
            + "			JOIN b_in_order t2 ON t1.serial_id = t2.id                                                                                      "
            + "			AND t1.serial_type = 'b_in_order'                                                                                               "
            + "         LEFT JOIN m_customer t3 ON t2.supplier_id = t3.id                                                                               "
            + "         LEFT JOIN v_dict_info t4 ON t4.code = '"+DictConstant.DICT_B_IN_PLAN_BUSINESS_TYPE +"' and t4.dict_value = t2.bill_type         "
            + "         LEFT JOIN m_owner t5 ON t2.owner_id = t5.id                                                                                     "
            + "			union all"
            + "		SELECT                                                                                                                              "
            + "			  t1.id,                                                                                                                        "
            + "           t2.contract_no,                                                                                                               "
            + "           t2.contract_num,                                                                                                              "
            + "		      t2.order_no,                                                                                                                  "
            + "		      t2.contract_dt,                                                                                                               "
            + "		      t2.ship_name,                                                                                                                 "
            + "		      ifnull(t3.short_name,t3.name) customer_name,                                                                                  "
            + "		      t1.serial_type,                                                                                                               "
            + "		      t4.label bill_type_name,                                                                                                      "
            + "		      ifnull(t5.short_name,t5.name) owner_name                                                                                      "
            + "		FROM                                                                                                                                "
            + "			b_order t1                                                                                                                      "
            + "			JOIN b_out_order t2 ON t1.serial_id = t2.id                                                                                     "
            + "			AND t1.serial_type = 'b_out_order'                                                                                              "
            + "         LEFT JOIN m_customer t3 ON t2.client_id = t3.id                                                                                 "
            + "         LEFT JOIN v_dict_info t4 ON t4.code = '"+DictConstant.DICT_B_IN_PLAN_BUSINESS_TYPE +"' and t4.dict_value = t2.bill_type         "
            + "         LEFT JOIN m_owner t5 ON t2.owner_id = t5.id                                                                                     "
            + "     )t7 on t.order_id = t7.id                                                                                                           "
//            + "  LEFT JOIN (select schedule_id,count(1) counts                                                                                          "
//            + "     from b_monitor where status != "+ DictConstant.DICT_B_MONITOR_STATUS_EIGHT +"                                                       "
//            + " group by schedule_id) t10 ON t.id = t10.schedule_id                                                                                     "
            + "  LEFT JOIN (select tab1.* from s_dict_data tab1 inner join s_dict_type tab2 on tab1.dict_type_id = tab2.id                              "
            + "             where tab2.code = '"+ DictConstant.DICT_B_SCHEDULE_STATUS +"') t8 on t8.dict_value = t.status                               "
            + "  LEFT JOIN b_in_plan_detail t9 ON t.in_plan_detail_id = t9.id                                                                           "
            + "  LEFT JOIN b_out_plan t11 ON t5.plan_id = t11.id                                                                                        "
            + "  LEFT JOIN m_owner t12 ON t.in_owner_id = t12.id                                                                                        "
            + "  LEFT JOIN m_customer t13 ON t.in_consignor_id = t13.id                                                                                 "
            + "  LEFT JOIN m_owner t14 ON t.out_owner_id = t14.id                                                                                       "
            + "  LEFT JOIN m_customer t15 ON t.out_consignor_id = t15.id                                                                                "
            + "  LEFT JOIN b_in_plan t16 ON t9.plan_id = t16.id                                                                                         "
            + "  LEFT JOIN b_schedule_info t17 on t17.schedule_id = t.id                                                                                "
            + "  LEFT JOIN m_customer t18 on t17.customer_id = t18.id                                                                                   "
            + " LEFT JOIN "
            + "(select t.schedule_id, sum(ifnull( t6.qty, t7.qty )) in_transit from b_monitor t left join b_schedule t1  ON t1.id = t.schedule_id       "
            + "    	LEFT JOIN b_monitor_out t6 ON t6.monitor_id = t.id                                                                                  "
            + "    	LEFT JOIN b_monitor_delivery t7 ON t7.monitor_id = t.id                                                                             "
            + "     where true                                                                                                                          "
            + "      and t.status in ( '" + DictConstant.DICT_B_MONITOR_STATUS_FOUR + "', '"+ DictConstant.DICT_B_MONITOR_STATUS_FIVE +"', '"+ DictConstant.DICT_B_MONITOR_STATUS_SIX +"')"
            + "      group by t.schedule_id ) t20 ON t20.schedule_id = t.id                                                                             "
            + "         LEFT JOIN v_dict_info t21 ON t21.code = '"+DictConstant.DICT_B_SCHEDULE_TYPE +"' and t21.dict_value = t.type                    "
            + "  LEFT JOIN (select tab1.label,tab1.dict_value from s_dict_data tab1 inner join s_dict_type tab2 on tab1.dict_type_id = tab2.id          "
            + "             where tab2.code = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE +"') t22 on t22.dict_value = t3.warehouse_type                     "
            + "  LEFT JOIN (select tab1.label,tab1.dict_value from s_dict_data tab1 inner join s_dict_type tab2 on tab1.dict_type_id = tab2.id          "
            + "             where tab2.code = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE +"') t23 on t23.dict_value = t4.warehouse_type                     "
            + "  LEFT JOIN b_carriage_order t24 ON t24.id = t.carriage_order_id                                                                         "
            + "  LEFT JOIN b_order t25 ON t25.serial_id = t9.order_id AND t25.serial_type = t9.order_type                                                "
            + "  LEFT JOIN b_order t26 ON t26.serial_id = t5.order_id AND t26.serial_type = t5.order_type                                                "
            + " LEFT JOIN "
            + "(select t.schedule_id ,ifnull(sum(t2.qty),0) as return_qty from b_monitor t left join b_schedule t1  ON t1.id = t.schedule_id       "
            + "    	LEFT JOIN b_return_relation t2 ON t2.serial_id = t.id AND t2.serial_type = '"+ SystemConstants.SERIAL_TYPE.B_MONITOR+"'             "
            + "    	AND  t2.STATUS = '"+ DictConstant.DICT_B_RETURN_RELATION_STATUS_TG +"'                                                             "
            + "     where true                                                                                                                          "
            + "      group by t.schedule_id ) t27 ON t27.schedule_id = t.id                                                                             "

            + " ,(select @row_num:=0) t19                                                                                                               "
            ;

    /**
     * 页面查询列表
     */
    @Select(" <script>   "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + common_select
            + "  where true                                                                                                                                                             "
            + "    and t.is_delete = '" + DictConstant.DICT_B_IS_DELETE_FALSE +"'                                                                                                       "
            + "    and (t.status = #{p1.status,jdbcType=VARCHAR} or #{p1.status,jdbcType=VARCHAR} is null or #{p1.status,jdbcType=VARCHAR} ='')                                         "
            + "    and (t.code like concat('%',#{p1.code,jdbcType=VARCHAR},'%') or #{p1.code,jdbcType=VARCHAR} is null)                                                                 "
            + "    and (t17.waybill_contract_no like concat('%',#{p1.waybill_contract_no,jdbcType=VARCHAR},'%') or #{p1.waybill_contract_no,jdbcType=VARCHAR} is null or #{p1.waybill_contract_no,jdbcType=VARCHAR} = '')                  "
            + "    and (concat(ifnull(t7.contract_no,''),ifnull(t.contract_no,'')) like concat('%',#{p1.contract_no,jdbcType=VARCHAR},'%') or #{p1.contract_no,jdbcType=VARCHAR} is null)"
            + "    and (concat(t6.name,t6.pm,t6.code,t6.spec) like concat('%',#{p1.goods_name,jdbcType=VARCHAR},'%') or #{p1.goods_name,jdbcType=VARCHAR} is null)                      "
            + "    and (t.out_type = #{p1.out_type,jdbcType=VARCHAR} or #{p1.out_type,jdbcType=VARCHAR} is null)                                                                        "
            + "    and (t.in_type = #{p1.in_type,jdbcType=VARCHAR} or #{p1.in_type,jdbcType=VARCHAR} is null)                                                                           "
            + "    and (concat(t15.name,t15.short_name) like concat('%',#{p1.out_consignor_name,jdbcType=VARCHAR},'%') or #{p1.out_consignor_name,jdbcType=VARCHAR} is null)            "
            + "    and (concat(t14.name,t14.short_name) like concat('%',#{p1.out_owner_name,jdbcType=VARCHAR},'%') or #{p1.out_owner_name,jdbcType=VARCHAR} is null or  #{p1.out_owner_name,jdbcType=VARCHAR} = '')"
            + "    and (concat(t13.name,t13.short_name) like concat('%',#{p1.in_consignor_name,jdbcType=VARCHAR},'%') or #{p1.in_consignor_name,jdbcType=VARCHAR} is null)              "
            + "    and (concat(t12.name,t12.short_name) like concat('%',#{p1.in_owner_name,jdbcType=VARCHAR},'%') or #{p1.in_owner_name,jdbcType=VARCHAR} is null or #{p1.in_owner_name,jdbcType=VARCHAR} = '') "
            + "    and (concat(t3.name,t3.short_name) like concat('%',#{p1.out_warehouse_name,jdbcType=VARCHAR},'%') or #{p1.out_warehouse_name,jdbcType=VARCHAR} is null)              "
            + "    and (concat(t4.name,t4.short_name) like concat('%',#{p1.in_warehouse_name,jdbcType=VARCHAR},'%') or #{p1.in_warehouse_name,jdbcType=VARCHAR} is null)                "
            + "    and (t.out_plan_code like concat('%',#{p1.out_plan_code,jdbcType=VARCHAR},'%') or #{p1.out_plan_code,jdbcType=VARCHAR} is null)                                      "
            + "    and (t.in_plan_code like concat('%',#{p1.in_plan_code,jdbcType=VARCHAR},'%') or #{p1.in_plan_code,jdbcType=VARCHAR} is null)                                         "
            + "    and (concat(t18.name, t18.short_name) like concat('%',#{p1.customer_name,jdbcType=VARCHAR},'%') or #{p1.customer_name,jdbcType=VARCHAR} is null or #{p1.customer_name,jdbcType=VARCHAR} = '')"
            + "    and (DATE_FORMAT(t.c_time, '%Y%m%d' ) &gt;= DATE_FORMAT(#{p1.start_time,jdbcType=DATE}, '%Y%m%d' ) or #{p1.start_time,jdbcType=DATE} is null)                         "
            + "    and (DATE_FORMAT(t.c_time, '%Y%m%d' ) &lt;= DATE_FORMAT(#{p1.over_time,jdbcType=DATE}, '%Y%m%d' ) or #{p1.over_time,jdbcType=DATE} is null)                           "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')                                                                "
            + "   <if test='p1.status_list != null and p1.status_list.length != 0' >                                                                                                     "
            + "    and t.status in                                                                                                                                                       "
            + "        <foreach collection='p1.status_list' item='item' index='index' open='(' separator=',' close=')'>                                                                  "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "   <if test='p1.out_warehouse_types != null and p1.out_warehouse_types.length != 0' >                                                                                     "
            + "    and t3.warehouse_type in                                                                                                                                              "
            + "        <foreach collection='p1.out_warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                          "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "   <if test='p1.in_warehouse_types != null and p1.in_warehouse_types.length != 0' >                                                                                       "
            + "    and t4.warehouse_type in                                                                                                                                              "
            + "        <foreach collection='p1.in_warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                           "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "    and (t.type = #{p1.type,jdbcType=VARCHAR} or #{p1.type,jdbcType=VARCHAR} is null or #{p1.type,jdbcType=VARCHAR} = '')                                                 "

            + "   <if test='p1.type_list != null and p1.type_list.length != 0' >                                                                                                         "
            + "    and t.type in                                                                                                                                                         "
            + "        <foreach collection='p1.type_list' item='item' index='index' open='(' separator=',' close=')'>                                                                    "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "

            + "     ${p1.params.dataScopeAnnotation}                                                                                                                                     "
            + "</script>      ")
    IPage<BScheduleVo> selectPage(Page page, @Param("p1") BScheduleVo searchCondition);

    /**
     * 查询更新 / 新增 后数据
     * @param id
     * @return
     */
    @Select(" <script>   "
            + common_select
            + "  where true                                                                                                                                                             "
            + "    and t.id = #{p1}                                                                                     "
            + "</script>      ")
    BScheduleVo selectListById(@Param("p1") Integer id);


    /**
     * 查询详情
     */
    @Select("    "
            + common_select
            + "  where true                                                                                             "
            + "    and (t.id = #{p1.id,jdbcType=INTEGER})                                                               "
            + "      ")
    BScheduleVo getDetail(@Param("p1") BScheduleVo searchCondition);

    /**
     * 入库监管id查询调度
     */
    @Select("                                                                                                           "
            + "     SELECT                                                                                              "
            + "            t2.*                                                                                         "
            + "     FROM                                                                                                "
            + "  	       b_monitor_in t                                                                               "
            + "            LEFT JOIN b_monitor t1 ON t.monitor_id = t1.id                                               "
            + "            LEFT JOIN b_schedule t2 ON t1.schedule_id = t2.id                                            "
            + "     where true                                                                                          "
            + "            and (t.id =  #{p1,jdbcType=INTEGER} or #{p1,jdbcType=INTEGER} is null)                       "
            + "     ")
    BScheduleEntity selectByMonitorInId(@Param("p1") Integer id);

    /**
     * 出库监管id查询调度
     */
    @Select("                                                                                                           "
            + "     SELECT                                                                                              "
            + "            t2.*                                                                                         "
            + "     FROM                                                                                                "
            + "  	       b_monitor_out t                                                                              "
            + "            LEFT JOIN b_monitor t1 ON t.monitor_id = t1.id                                               "
            + "            LEFT JOIN b_schedule t2 ON t1.schedule_id = t2.id                                            "
            + "     where true                                                                                          "
            + "            and (t.id =  #{p1,jdbcType=INTEGER} or #{p1,jdbcType=INTEGER} is null)                       "
            + "     ")
    BScheduleEntity selectByMonitorOutId(@Param("p1") Integer id);

    /**
     * id查询
     */
    @Select("    "
            + "  SELECT                                                                                                 "
            + "         t.id,                                                                                           "
            + "         t.code,                                                                                         "
            + "         t.status,                                                                                       "
            + "         t.out_plan_detail_code,                                                                         "
            + "         t.out_plan_code,                                                                                "
            + "         t.out_plan_detail_id,                                                                           "
            + "         t.sku_id,                                                                                       "
            + "         t.sku_code,                                                                                     "
            + "         t.out_warehouse_id,                                                                             "
            + "         t.out_location_id,                                                                              "
            + "         t.out_bin_id,                                                                                   "
            + "         t.out_schedule_qty,                                                                             "
            + "         t.out_operated_qty,                                                                             "
            + "         t.out_balance_qty,                                                                              "
            + "         t.out_unit_id,                                                                                  "
            + "         t.in_schedule_qty,                                                                              "
            + "         t.in_balance_qty,                                                                               "
            + "         t.in_operated_qty,                                                                              "
            + "         t.in_unit_id,                                                                                   "
            + "         t.c_time,                                                                                       "
            + "         t.u_time,                                                                                       "
            + "         t.c_id,                                                                                         "
            + "         t.u_id,                                                                                         "
            + "         t.dbversion,                                                                                    "
            + "         t.in_warehouse_id,                                                                              "
            + "         t2.id as in_location_id,                                                                        "
            + "         t3.id as in_bin_id                                                                              "
            + "     from                                                                                                "
            + "         b_schedule t                                                                                    "
            + "         LEFT JOIN m_warehouse t1 on t1.id=t.in_warehouse_id                                                                                   "
            + "         LEFT JOIN m_location t2 ON t1.id = t2.warehouse_id AND t2.enable = '"+ SystemConstants.ENABLE_TRUE + "'                             "
            + "         LEFT JOIN m_bin t3 ON t3.warehouse_id = t1.id  AND t3.location_id = t2.id AND t3.enable = '"+ SystemConstants.ENABLE_TRUE + "'      "
            + "     where true                                                                                                                                "
            + "         and t1.enable_location = '"+ SystemConstants.ENABLE_FALSE +"'                                                                       "
            + "         and t1.enable_bin = '"+ SystemConstants.ENABLE_FALSE +"'                                                                            "
            + "         and t1.enable = '"+ SystemConstants.ENABLE_TRUE +"'                                                                                 "
            + "         and (t.id = #{p1,jdbcType=INTEGER} or #{p1,jdbcType=INTEGER} is null)                                                                 "
            + "      ")
    BScheduleEntity selectId(@Param("p1") Integer id);

    /**
     * 出库监管id查询调度
     */
    @Select("                                                                                                           "
            + "     SELECT                                                                                              "
            + "            t2.*                                                                                         "
            + "     FROM                                                                                                "
            + "  	       b_monitor_delivery t                                                                         "
            + "            LEFT JOIN b_monitor t1 ON t.monitor_id = t1.id                                               "
            + "            LEFT JOIN b_schedule t2 ON t1.schedule_id = t2.id                                            "
            + "     where true                                                                                          "
            + "            and (t.id =  #{p1,jdbcType=INTEGER})                                                         "
            + "     ")
    BScheduleEntity selectByMonitorDeliveryId(@Param("p1") Integer id);

    /**
     * 出库监管id查询调度
     */
    @Select("                                                                                                           "
            + "				SELECT                                                                                                                                                                                            "
            + "					t.*,                                                                                                                                                                                          "
            + "					ifnull( sum( t2.out_qty ), 0 ) out_qty,                                                                                                                                                       "
            + "					ifnull( sum( t2.in_qty ), 0 ) in_qty,                                                                                                                                                         "
            + "					ifnull( t.in_schedule_qty - ifnull( sum( t2.in_qty ),0), 0 ) in_balance,                                                                                                                      "
            + "					ifnull( t.out_schedule_qty - ifnull( sum( t2.out_qty ),0), 0 ) out_balance                                                                                                                    "
            + "				FROM                                                                                                                                                                                              "
            + "					b_schedule t                                                                                                                                                                                  "
            + "					LEFT JOIN (                                                                                                                                                                                   "
            + "				SELECT                                                                                                                                                                                            "
            + "					t1.schedule_id,                                                                                                                                                                               "
            + "					ifnull( sum( t2.qty ), 0 ) in_qty,                                                                                                                                                            "
            + "					ifnull( sum( t3.qty ), 0 ) out_qty                                                                                                                                                            "
            + "				FROM                                                                                                                                                                                              "
            + "					b_monitor t1                                                                                                                                                                                  "
            + "					LEFT JOIN ( SELECT tab1.qty, tab1.monitor_id FROM b_monitor_in tab1 UNION ALL SELECT tab2.qty, tab2.monitor_id FROM b_monitor_unload tab2 ) t2 ON t1.id = t2.monitor_id                       "
            + "					LEFT JOIN ( SELECT tab1.qty, tab1.monitor_id FROM b_monitor_out tab1 UNION ALL SELECT tab2.qty, tab2.monitor_id FROM b_monitor_delivery tab2 ) t3 ON t1.id = t3.monitor_id                    "
            + "				WHERE                                                                                                                                                                                             "
            + "					t1.STATUS <> '"+DictConstant.DICT_B_MONITOR_STATUS_EIGHT+"'                                                                                                                                   "
            + "				GROUP BY                                                                                                                                                                                          "
            + "					t1.schedule_id                                                                                                                                                                                "
            + "					) t2 ON t.id = t2.schedule_id                                                                                                                                                                 "
            + "     where true                                                                                                                                                                                                "
            + "            and (t.id =  #{p1,jdbcType=INTEGER})                                                                                                                                                               "
            + "				GROUP BY                                                                                                                                                                                          "
            + "					t.id                                                                                                                                                                                          "
            + "     ")
    BScheduleVo selectByScheduleId(@Param("p1") Integer id);

    /**
     * id查询
     */
    @Select(" "
            + "	UPDATE b_schedule tt1                                                                                                                                     "
            + "	INNER JOIN (                                                                                                                                              "
            + "		SELECT                                                                                                                                                "
            + "			t1.id,                                                                                                                                            "
            + "			t1.CODE,                                                                                                                                          "
            + "			t1.c_time,                                                                                                                                        "
            + "			t1.in_schedule_qty,                                                                                                                               "
            + "			t1.in_balance_qty,                                                                                                                                "
            + "			t1.in_schedule_qty - ifnull( t2.in_qty, 0 ),                                                                                                      "
            + "		CASE                                                                                                                                                  "
            + "				                                                                                                                                              "
            + "				WHEN t1.in_schedule_qty - ifnull( t2.in_qty, 0 ) < 0 THEN                                                                                     "
            + "				0 ELSE t1.in_schedule_qty - ifnull( t2.in_qty, 0 )                                                                                            "
            + "			END in_balance,                                                                                                                                   "
            + "		t1.in_operated_qty,                                                                                                                                   "
            + "		ifnull( t2.in_qty, 0 ) in_qty,                                                                                                                        "
            + "		t1.out_schedule_qty,                                                                                                                                  "
            + "		t1.out_balance_qty,                                                                                                                                   "
            + "		t1.out_schedule_qty - ifnull( t2.out_qty, 0 ),                                                                                                        "
            + "	CASE                                                                                                                                                      "
            + "			                                                                                                                                                  "
            + "			WHEN t1.out_schedule_qty - ifnull( t2.out_qty, 0 ) < 0 THEN                                                                                       "
            + "			0 ELSE t1.out_schedule_qty - ifnull( t2.out_qty, 0 )                                                                                              "
            + "		END out_balance,                                                                                                                                      "
            + "		t1.out_operated_qty,                                                                                                                                  "
            + "		ifnull( t2.out_qty, 0 ) out_qty                                                                                                                       "
            + "	FROM                                                                                                                                                      "
            + "		b_schedule t1                                                                                                                                         "
            + "		LEFT JOIN (                                                                                                                                           "
//            + "		SELECT                                                                                                                                                "
//            + "			tt1.id,                                                                                                                                           "
//            + "			sum( tt2.net_weight ) in_qty,                                                                                                                     "
//            + "			sum( tt3.net_weight ) out_qty,                                                                                                                    "
//            + "			tt1.schedule_id                                                                                                                                   "
//            + "		FROM                                                                                                                                                  "
//            + "			b_monitor tt1                                                                                                                                     "
//            + "			LEFT JOIN ( SELECT * FROM b_monitor_in tab1 UNION ALL SELECT * FROM b_monitor_unload tab2 ) tt2 ON tt1.id = tt2.monitor_id                        "
//            + "			LEFT JOIN ( SELECT * FROM b_monitor_out tab1 UNION ALL SELECT * FROM b_monitor_delivery tab2 ) tt3 ON tt1.id = tt3.monitor_id                     "
//            + "		WHERE                                                                                                                                                 "
//            + "			tt1.STATUS <> '8'                                                                                                                                 "
//            + "		GROUP BY                                                                                                                                              "
//            + "			tt1.schedule_id                                                                                                                                   "

            // 算上备份的
            +  "      SELECT                                                                                                                                              "
            +  "  		tab1.schedule_id,                                                                                                                                 "
            +  "  		sum( tab1.in_qty ) in_qty,                                                                                                                        "
            +  "  		sum( tab1.out_qty ) out_qty                                                                                                                       "
            +  "  	FROM                                                                                                                                                  "
            +  "  		(                                                                                                                                                 "
            +  "  		SELECT                                                                                                                                            "
            +  "  			sum( tt2.net_weight ) in_qty,                                                                                                                 "
            +  "  			sum( tt3.net_weight ) out_qty,                                                                                                                "
            +  "  			tt1.schedule_id                                                                                                                               "
            +  "  		FROM                                                                                                                                              "
            +  "  			b_monitor tt1                                                                                                                                 "
            +  "  			LEFT JOIN ( SELECT * FROM b_monitor_in tab1 UNION ALL SELECT * FROM b_monitor_unload tab2 ) tt2 ON tt1.id = tt2.monitor_id                    "
            +  "  			LEFT JOIN ( SELECT * FROM b_monitor_out tab1 UNION ALL SELECT * FROM b_monitor_delivery tab2 ) tt3 ON tt1.id = tt3.monitor_id                 "
            +  "  		WHERE                                                                                                                                             "
            +  "  			tt1.STATUS <> '8'                                                                                                                             "
            +  "  		GROUP BY                                                                                                                                          "
            +  "  			tt1.schedule_id UNION ALL                                                                                                                     "
            +  "  		SELECT                                                                                                                                            "
            +  "  			sum( tt1.in_qty ) in_qty,                                                                                                                     "
            +  "  			sum( tt1.out_qty ) out_qty,                                                                                                                   "
            +  "  			tt1.schedule_id                                                                                                                               "
            +  "  		FROM                                                                                                                                              "
            +  "  			b_monitor_backup tt1                                                                                                                          "
            +  "  		GROUP BY                                                                                                                                          "
            +  "  			tt1.schedule_id                                                                                                                               "
            +  "  		) tab1                                                                                                                                            "
            +  "  	GROUP BY                                                                                                                                              "
            +  "  		tab1.schedule_id                                                                                                                                  "

            + "		) t2 ON t1.id = t2.schedule_id                                                                                                                        "
            + "		) tt2 ON tt1.id = tt2.id                                                                                                                              "
            + "		SET tt1.in_operated_qty = ifnull(tt2.in_qty, 0),                                                                                                                 "
            + "		tt1.out_operated_qty = ifnull(tt2.out_qty, 0),                                                                                                                   "
            + "		tt1.in_balance_qty = ifnull(tt2.in_balance, 0),                                                                                                                  "
            + "		tt1.out_balance_qty = ifnull(tt2.out_balance, 0)                                                                                                                 "
            + "            where tt1.id =  #{p1,jdbcType=INTEGER}                                                                                                         "
            + "     ")
    void updateScheduleQty(@Param("p1") Integer id);

    @Select(
            "   <script>                                                                                                                                                 "
                    +  common_select
                    + "  where true                                                                                                                                              "
                    + "     and t.id in                                                                                                                                          "
                    + "        <foreach collection='p1' item='item' index='index' open='(' separator=',' close=')'>             "
                    + "         #{item.id,jdbcType=INTEGER}                                                                     "
                    + "        </foreach>                                                                                       "
                    + "  </script>    ")
    List<BScheduleExcelVo> selectExportList(@Param("p1") List<BScheduleVo> searchCondition);

    /**
     * 没有分页，按id筛选条件
     * @param searchCondition
     * @return
     */
    @Select("   <script>   "
            + "  select * from b_schedule t                                                                             "
            + "  where t.id in                                                                                          "
            + "        <foreach collection='p1' item='item' index='index' open='(' separator=',' close=')'>             "
            + "         #{item.id,jdbcType=INTEGER}                                                                     "
            + "        </foreach>                                                                                       "
            + "  </script>    ")
    List<BScheduleEntity> selectIdsIn(@Param("p1") List<BScheduleVo> searchCondition);

    @Select("<script>    "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + common_select
            + "  where true                                                                                                                                                             "
            + "    and t.is_delete = '" + DictConstant.DICT_B_IS_DELETE_FALSE +"'                                                                                                       "
            + "    and (t.status = #{p1.status,jdbcType=VARCHAR} or #{p1.status,jdbcType=VARCHAR} is null or #{p1.status,jdbcType=VARCHAR} ='')                                         "
            + "    and (t.code like concat('%',#{p1.code,jdbcType=VARCHAR},'%') or #{p1.code,jdbcType=VARCHAR} is null)                                                                 "
            + "    and (t17.waybill_contract_no like concat('%',#{p1.waybill_contract_no,jdbcType=VARCHAR},'%') or #{p1.waybill_contract_no,jdbcType=VARCHAR} is null)                  "
            + "    and (concat(ifnull(t7.contract_no,''),ifnull(t.contract_no,'')) like concat('%',#{p1.contract_no,jdbcType=VARCHAR},'%') or #{p1.contract_no,jdbcType=VARCHAR} is null)"
            + "    and (concat(t6.name,t6.pm,t6.code,t6.spec) like concat('%',#{p1.goods_name,jdbcType=VARCHAR},'%') or #{p1.goods_name,jdbcType=VARCHAR} is null)                      "
            + "    and (t.out_type = #{p1.out_type,jdbcType=VARCHAR} or #{p1.out_type,jdbcType=VARCHAR} is null)                                                                        "
            + "    and (t.in_type = #{p1.in_type,jdbcType=VARCHAR} or #{p1.in_type,jdbcType=VARCHAR} is null)                                                                           "
            + "    and (concat(t15.name,t15.short_name) like concat('%',#{p1.out_consignor_name,jdbcType=VARCHAR},'%') or #{p1.out_consignor_name,jdbcType=VARCHAR} is null)            "
            + "    and (concat(t14.name,t14.short_name) like concat('%',#{p1.out_owner_name,jdbcType=VARCHAR},'%') or #{p1.out_owner_name,jdbcType=VARCHAR} is null or #{p1.out_owner_name,jdbcType=VARCHAR} = '')                    "
            + "    and (concat(t13.name,t13.short_name) like concat('%',#{p1.in_consignor_name,jdbcType=VARCHAR},'%') or #{p1.in_consignor_name,jdbcType=VARCHAR} is null)              "
            + "    and (concat(t12.name,t12.short_name) like concat('%',#{p1.in_owner_name,jdbcType=VARCHAR},'%') or #{p1.in_owner_name,jdbcType=VARCHAR} is null or #{p1.in_owner_name,jdbcType=VARCHAR} = '')                      "
            + "    and (concat(t3.name,t3.short_name) like concat('%',#{p1.out_warehouse_name,jdbcType=VARCHAR},'%') or #{p1.out_warehouse_name,jdbcType=VARCHAR} is null)              "
            + "    and (concat(t4.name,t4.short_name) like concat('%',#{p1.in_warehouse_name,jdbcType=VARCHAR},'%') or #{p1.in_warehouse_name,jdbcType=VARCHAR} is null)                "
            + "    and (t.out_plan_code like concat('%',#{p1.out_plan_code,jdbcType=VARCHAR},'%') or #{p1.out_plan_code,jdbcType=VARCHAR} is null)                                      "
            + "    and (t.in_plan_code like concat('%',#{p1.in_plan_code,jdbcType=VARCHAR},'%') or #{p1.in_plan_code,jdbcType=VARCHAR} is null)                                         "
            + "    and (concat(t18.name, t18.short_name) like concat('%',#{p1.customer_name,jdbcType=VARCHAR},'%') or #{p1.customer_name,jdbcType=VARCHAR} is null or #{p1.customer_name,jdbcType=VARCHAR} = '')"
            + "    and (DATE_FORMAT(t.c_time, '%Y%m%d' ) &gt;= DATE_FORMAT(#{p1.start_time,jdbcType=DATE}, '%Y%m%d' ) or #{p1.start_time,jdbcType=DATE} is null)                         "
            + "    and (DATE_FORMAT(t.c_time, '%Y%m%d' ) &lt;= DATE_FORMAT(#{p1.over_time,jdbcType=DATE}, '%Y%m%d' ) or #{p1.over_time,jdbcType=DATE} is null)                           "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')                                                                "
            + "   <if test='p1.status_list != null and p1.status_list.length != 0' >                                                                                                     "
            + "    and t.status in                                                                                                                                                       "
            + "        <foreach collection='p1.status_list' item='item' index='index' open='(' separator=',' close=')'>                                                                  "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "   <if test='p1.out_warehouse_types != null and p1.out_warehouse_types.length != 0' >                                                                                         "
            + "    and t3.warehouse_type in                                                                                                                                              "
            + "        <foreach collection='p1.out_warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                          "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "   <if test='p1.in_warehouse_types != null and p1.in_warehouse_types.length != 0' >                                                                                          "
            + "    and t4.warehouse_type in                                                                                                                                              "
            + "        <foreach collection='p1.in_warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                           "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "    and (t.type = #{p1.type,jdbcType=VARCHAR} or #{p1.type,jdbcType=VARCHAR} is null or #{p1.type,jdbcType=VARCHAR} = '')                                                 "

            + "   <if test='p1.type_list != null and p1.type_list.length != 0' >                                                                                                         "
            + "    and t.type in                                                                                                                                                         "
            + "        <foreach collection='p1.type_list' item='item' index='index' open='(' separator=',' close=')'>                                                                    "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "

            + "     ${p1.params.dataScopeAnnotation}                                                                                                                                    "
            + "</script>      ")
    List<BScheduleExcelVo> selectExportListAll(@Param("p1") BScheduleVo searchCondition);

    @Select("<script>"
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "     SELECT                                                                                                                                                               "
            + "            sum(t.out_operated_qty) out_operated_qty,                                                                                                                    "
            + "            sum(t.in_operated_qty) in_operated_qty,                                                                                                                      "
            + "            sum(CASE WHEN t.out_operated_qty-t.out_schedule_qty > 0 then t.out_operated_qty-t.out_schedule_qty ELSE 0 END) out_over_qty,                                 "
            + "            sum(CASE WHEN t.in_operated_qty-t.in_schedule_qty > 0 then t.in_operated_qty-t.in_schedule_qty ELSE 0 END) in_over_qty,                                       "
            + "            sum(t19.return_qty) count_return_qty                                                                                                                         "
            + "       FROM                                                                                                                                                              "
            + "  	       b_schedule t                                                                                                                                                 "
            + "  LEFT JOIN m_staff t1 ON t.c_id = t1.id                                                                                                                                 "
            + "  LEFT JOIN m_staff t2 ON t.u_id = t2.id                                                                                                                                 "
            + "  LEFT JOIN m_warehouse t3 ON t.out_warehouse_id = t3.id                                                                                                                 "
            + "  LEFT JOIN m_warehouse t4 ON t.in_warehouse_id = t4.id                                                                                                                  "
            + "  LEFT JOIN b_out_plan_detail t5 ON t.in_plan_detail_id = t5.id                                                                                                          "
            + "  LEFT JOIN m_goods_spec t6 ON t.sku_id = t6.id                                                                                                                          "
            + "  LEFT JOIN (                                                                                                                                                            "
            + "		SELECT                                                                                                                                                              "
            + "			  t1.id,                                                                                                                                                        "
            + "           t2.contract_no,                                                                                                                                               "
            + "           t2.contract_num,                                                                                                                                              "
            + "		      t2.order_no,                                                                                                                                                  "
            + "		      t2.contract_dt,                                                                                                                                               "
            + "		      t2.ship_name,                                                                                                                                                 "
            + "		      ifnull(t3.short_name,t3.name) customer_name,                                                                                                                  "
            + "		      t1.serial_type,                                                                                                                                               "
            + "		      t4.label bill_type_name,                                                                                                                                      "
            + "		      ifnull(t5.short_name,t5.name) owner_name                                                                                                                      "
            + "		FROM                                                                                                                                                                "
            + "			b_order t1                                                                                                                                                      "
            + "			JOIN b_in_order t2 ON t1.serial_id = t2.id                                                                                                                      "
            + "			AND t1.serial_type = 'b_in_order'                                                                                                                               "
            + "         LEFT JOIN m_customer t3 ON t2.supplier_id = t3.id                                                                                                               "
            + "         LEFT JOIN v_dict_info t4 ON t4.code = '"+DictConstant.DICT_B_IN_PLAN_BUSINESS_TYPE +"' and t4.dict_value = t2.bill_type                                         "
            + "         LEFT JOIN m_owner t5 ON t2.owner_id = t5.id                                                                                                                     "
            + "			union all                                                                                                                                                       "
            + "		SELECT                                                                                                                                                              "
            + "			  t1.id,                                                                                                                                                        "
            + "           t2.contract_no,                                                                                                                                               "
            + "           t2.contract_num,                                                                                                                                              "
            + "		      t2.order_no,                                                                                                                                                  "
            + "		      t2.contract_dt,                                                                                                                                               "
            + "		      t2.ship_name,                                                                                                                                                 "
            + "		      ifnull(t3.short_name,t3.name) customer_name,                                                                                                                  "
            + "		      t1.serial_type,                                                                                                                                               "
            + "		      t4.label bill_type_name,                                                                                                                                      "
            + "		      ifnull(t5.short_name,t5.name) owner_name                                                                                                                      "
            + "		FROM                                                                                                                                                                "
            + "			b_order t1                                                                                                                                                      "
            + "			JOIN b_out_order t2 ON t1.serial_id = t2.id                                                                                                                     "
            + "			AND t1.serial_type = 'b_out_order'                                                                                                                              "
            + "         LEFT JOIN m_customer t3 ON t2.client_id = t3.id                                                                                                                 "
            + "         LEFT JOIN v_dict_info t4 ON t4.code = '"+DictConstant.DICT_B_IN_PLAN_BUSINESS_TYPE +"' and t4.dict_value = t2.bill_type                                         "
            + "         LEFT JOIN m_owner t5 ON t2.owner_id = t5.id                                                                                                                     "
            + "     )t7 on t.order_id = t7.id                                                                                                                                           "
            + "  LEFT JOIN (select schedule_id,count(1) counts                                                                                                                          "
            + "     from b_monitor where status != "+ DictConstant.DICT_B_MONITOR_STATUS_EIGHT +"                                                                                       "
            + " group by schedule_id) t10 ON t.id = t10.schedule_id                                                                                                                     "
            + "  LEFT JOIN (select tab1.* from s_dict_data tab1 inner join s_dict_type tab2 on tab1.dict_type_id = tab2.id                                                              "
            + "             where tab2.code = '"+ DictConstant.DICT_B_SCHEDULE_STATUS +"') t8 on t8.dict_value = t.status                                                               "
            + "  LEFT JOIN b_in_plan_detail t9 ON t.in_plan_detail_id = t9.id                                                                                                           "
            + "  LEFT JOIN b_out_plan t11 ON t5.plan_id = t11.id                                                                                                                        "
            + "  LEFT JOIN m_owner t12 ON t.in_owner_id = t12.id                                                                                                                        "
            + "  LEFT JOIN m_customer t13 ON t.in_consignor_id = t13.id                                                                                                                 "
            + "  LEFT JOIN m_owner t14 ON t.out_owner_id = t14.id                                                                                                                       "
            + "  LEFT JOIN m_customer t15 ON t.out_consignor_id = t15.id                                                                                                                "
            + "  LEFT JOIN b_in_plan t16 ON t9.plan_id = t16.id                                                                                                                         "
            + "  LEFT JOIN b_schedule_info t17 on t17.schedule_id = t.id                                                                                                                "
            + "  LEFT JOIN m_customer t18 on t17.customer_id = t18.id                                                                                                                   "
            + "  LEFT JOIN (select t.schedule_id ,ifnull(sum(t2.qty),0) as return_qty from b_monitor t left join b_schedule t1  ON t1.id = t.schedule_id       "
            + "    	LEFT JOIN b_return_relation t2 ON t2.serial_id = t.id AND t2.serial_type = '"+ SystemConstants.SERIAL_TYPE.B_MONITOR+"'             "
            + "    	AND  t2.STATUS = '"+ DictConstant.DICT_B_RETURN_RELATION_STATUS_TG +"'                                                             "
            + "     where true                                                                                                                          "
            + "      group by t.schedule_id ) t19 ON t19.schedule_id = t.id                                                                             "
            + "  where true                                                                                                                                                             "
            + "    and t.is_delete = '" + DictConstant.DICT_B_IS_DELETE_FALSE +"'                                                                                                       "
            + "    and (t.status = #{p1.status,jdbcType=VARCHAR} or #{p1.status,jdbcType=VARCHAR} is null or #{p1.status,jdbcType=VARCHAR} ='')                                         "
            + "    and (t.code like concat('%',#{p1.code,jdbcType=VARCHAR},'%') or #{p1.code,jdbcType=VARCHAR} is null)                                                                 "
            + "    and (t17.waybill_contract_no like concat('%',#{p1.waybill_contract_no,jdbcType=VARCHAR},'%') or #{p1.waybill_contract_no,jdbcType=VARCHAR} is null)                  "
            + "    and (concat(ifnull(t7.contract_no,''),ifnull(t.contract_no,'')) like concat('%',#{p1.contract_no,jdbcType=VARCHAR},'%') or #{p1.contract_no,jdbcType=VARCHAR} is null)"
            + "    and (concat(t6.name,t6.pm,t6.code,t6.spec) like concat('%',#{p1.goods_name,jdbcType=VARCHAR},'%') or #{p1.goods_name,jdbcType=VARCHAR} is null)                      "
            + "    and (t.out_type = #{p1.out_type,jdbcType=VARCHAR} or #{p1.out_type,jdbcType=VARCHAR} is null)                                                                        "
            + "    and (t.in_type = #{p1.in_type,jdbcType=VARCHAR} or #{p1.in_type,jdbcType=VARCHAR} is null)                                                                           "
            + "    and (concat(t15.name,t15.short_name) like concat('%',#{p1.out_consignor_name,jdbcType=VARCHAR},'%') or #{p1.out_consignor_name,jdbcType=VARCHAR} is null)            "
            + "    and (concat(t14.name,t14.short_name) like concat('%',#{p1.out_owner_name,jdbcType=VARCHAR},'%') or #{p1.out_owner_name,jdbcType=VARCHAR} is null or #{p1.out_owner_name,jdbcType=VARCHAR} = '')                    "
            + "    and (concat(t13.name,t13.short_name) like concat('%',#{p1.in_consignor_name,jdbcType=VARCHAR},'%') or #{p1.in_consignor_name,jdbcType=VARCHAR} is null)              "
            + "    and (concat(t12.name,t12.short_name) like concat('%',#{p1.in_owner_name,jdbcType=VARCHAR},'%') or #{p1.in_owner_name,jdbcType=VARCHAR} is null or #{p1.in_owner_name,jdbcType=VARCHAR} = '')                      "
            + "    and (concat(t3.name,t3.short_name) like concat('%',#{p1.out_warehouse_name,jdbcType=VARCHAR},'%') or #{p1.out_warehouse_name,jdbcType=VARCHAR} is null)              "
            + "    and (concat(t4.name,t4.short_name) like concat('%',#{p1.in_warehouse_name,jdbcType=VARCHAR},'%') or #{p1.in_warehouse_name,jdbcType=VARCHAR} is null)                "
            + "    and (t.out_plan_code like concat('%',#{p1.out_plan_code,jdbcType=VARCHAR},'%') or #{p1.out_plan_code,jdbcType=VARCHAR} is null)                                      "
            + "    and (t.in_plan_code like concat('%',#{p1.in_plan_code,jdbcType=VARCHAR},'%') or #{p1.in_plan_code,jdbcType=VARCHAR} is null)                                         "
            + "    and (concat(t18.name, t18 .short_name) like concat('%',#{p1.customer_name,jdbcType=VARCHAR},'%') or #{p1.customer_name,jdbcType=VARCHAR} is null or #{p1.customer_name,jdbcType=VARCHAR} = '')"
            + "    and (DATE_FORMAT(t.c_time, '%Y%m%d' ) &gt;= DATE_FORMAT(#{p1.start_time,jdbcType=DATE}, '%Y%m%d' ) or #{p1.start_time,jdbcType=DATE} is null)                         "
            + "    and (DATE_FORMAT(t.c_time, '%Y%m%d' ) &lt;= DATE_FORMAT(#{p1.over_time,jdbcType=DATE}, '%Y%m%d' ) or #{p1.over_time,jdbcType=DATE} is null)                           "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')                                                                "
            + "   <if test='p1.status_list != null and p1.status_list.length != 0' >                                                                                                     "
            + "    and t.status in                                                                                                                                                       "
            + "        <foreach collection='p1.status_list' item='item' index='index' open='(' separator=',' close=')'>                                                                  "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "   <if test='p1.out_warehouse_types != null and p1.out_warehouse_types.length != 0' >                                                                                     "
            + "    and t3.warehouse_type in                                                                                                                                              "
            + "        <foreach collection='p1.out_warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                          "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "   <if test='p1.in_warehouse_types != null and p1.in_warehouse_types.length != 0' >                                                                                       "
            + "    and t4.warehouse_type in                                                                                                                                              "
            + "        <foreach collection='p1.in_warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                           "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "    and (t.type = #{p1.type,jdbcType=VARCHAR} or #{p1.type,jdbcType=VARCHAR} is null or #{p1.type,jdbcType=VARCHAR} = '')                                                 "

            + "   <if test='p1.type_list != null and p1.type_list.length != 0' >                                                                                                         "
            + "    and t.type in                                                                                                                                                         "
            + "        <foreach collection='p1.type_list' item='item' index='index' open='(' separator=',' close=')'>                                                                    "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "

            + "     ${p1.params.dataScopeAnnotation}                                                                                                                                     "
            + " </script>"
    )
    BScheduleSumVo selectSumData(@Param("p1") BScheduleVo searchCondition);

//    /**
//     * 根据 sku_id, out_owner_id, out_warehouse_id 查询可调度库存
//     *
//     * @param searchCondition
//     * @return
//     */
//    @Select(    "  SELECT                                                          "
//            +   "  	( t3.qty_avaible - IFNULL(t2.out_balance_qty,0) ) qty_avaible  "
//            +   "  FROM                                                            "
//            +   "  	(                                                              "
//            +   "  	SELECT                                                         "
//            +   "  		SUM( t.qty_avaible ) qty_avaible,                          "
//            +   "  		t.sku_id,                                                  "
//            +   "  		t.owner_id,                                                "
//            +   "  		t.warehouse_id                                             "
//            +   "  	FROM                                                           "
//            +   "  		m_inventory t                                              "
//            +   "  	WHERE                                                          "
//            +   "  		t.sku_id = #{p1.sku_id}                                    "
//            +   "  		AND t.owner_id = #{p1.out_owner_id}                        "
//            +   "  		AND t.warehouse_id = #{p1.out_warehouse_id}                "
//            +   "  	GROUP BY                                                       "
//            +   "  		t.sku_id,                                                  "
//            +   "  		t.owner_id,                                                "
//            +   "  		t.warehouse_id                                             "
//            +   "  	) t3                                                           "
//            +   "  	LEFT JOIN (                                                    "
//            +   "  	SELECT                                                         "
//            +   "  		t1.sku_id,                                                 "
//            +   "  		t1.out_owner_id,                                           "
//            +   "  		t1.out_warehouse_id,                                       "
//            +   "  		SUM( t1.out_balance_qty ) out_balance_qty                  "
//            +   "  	FROM                                                           "
//            +   "  		b_schedule t1                                              "
//            +   "  	WHERE                                                          "
//            +   "  		t1.`status` in ('0' ,'2' ,'3' )                            "
//            +   "  		AND t1.out_warehouse_id = #{p1.out_warehouse_id}           "
//            +   "  		AND t1.out_owner_id = #{p1.out_owner_id}                   "
//            +   "  		AND t1.sku_id =  #{p1.sku_id}                              "
//            +   "  	GROUP BY                                                       "
//            +   "  		t1.sku_id,                                                 "
//            +   "  		t1.out_owner_id,                                           "
//            +   "  		t1.out_warehouse_id                                        "
//            +   "  	) t2 ON t3.sku_id = t2.sku_id                                  "
//            +   "  	AND t3.owner_id = t2.out_owner_id                              "
//            +   "  	AND t3.warehouse_id = t2.out_warehouse_id                      ")
//    BigDecimal getScheduleQty(@Param("p1") BScheduleVo searchCondition);

    /**
     * 根据 order_id 查询可调度库存
     *
     * @param searchCondition
     * @return
     */
    @Select(    "  "
            + "		SELECT                                                                      "
            + "			ifnull(                                                                 "
            + "				SUM( CASE WHEN t1.status = '1'                                      "
            + "					THEN t1.out_operated_qty                                        "
            + "					ELSE t1.out_schedule_qty END ), 0                               "
            + "			) qty                                                                   "
            + "		FROM                                                                        "
            + "			b_schedule t1                                                           "
            + "		WHERE                                                                       "
            + "			t1.status IN ( '0', '1', '2', '3' )  and t1.type <> '2'                 "
            + "  	    AND t1.order_id = #{p1.order_id}                                        "
            + "		GROUP BY                                                                    "
            + "			t1.order_id                                                             "
            + "  ")
    BigDecimal getScheduleQty(@Param("p1") BScheduleVo searchCondition);

    /**
     * 导出条数查询
     * @param searchCondition
     * @return
     */
    @Select("<script>     "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "     SELECT                                                                                                                              "
            + "            count(1)                                                                                                                     "
            + "       FROM                                                                                                                              "
            + "  	       b_schedule t                                                                                                                 "
            + "  LEFT JOIN m_warehouse t3 ON t.out_warehouse_id = t3.id                                                                                 "
            + "  LEFT JOIN m_warehouse t4 ON t.in_warehouse_id = t4.id                                                                                  "
            + "  LEFT JOIN b_out_plan_detail t5 ON t.in_plan_detail_id = t5.id                                                                          "
            + "  LEFT JOIN m_goods_spec t6 ON t.sku_id = t6.id                                                                                          "
            + "  LEFT JOIN (                                                                                                                            "
            + "		SELECT                                                                                                                              "
            + "			  t1.id,                                                                                                                        "
            + "           t2.contract_no                                                                                                                "
            + "		FROM                                                                                                                                "
            + "			b_order t1                                                                                                                      "
            + "			JOIN b_in_order t2 ON t1.serial_id = t2.id                                                                                      "
            + "			AND t1.serial_type = 'b_in_order'                                                                                               "
            + "			union all"
            + "		SELECT                                                                                                                              "
            + "			  t1.id,                                                                                                                        "
            + "           t2.contract_no                                                                                                                "
            + "		FROM                                                                                                                                "
            + "			b_order t1                                                                                                                      "
            + "			JOIN b_out_order t2 ON t1.serial_id = t2.id                                                                                     "
            + "			AND t1.serial_type = 'b_out_order'                                                                                              "
            + "     )t7 on t.order_id = t7.id                                                                                                           "
            + "  LEFT JOIN m_owner t12 ON t.in_owner_id = t12.id                                                                                        "
            + "  LEFT JOIN m_customer t13 ON t.in_consignor_id = t13.id                                                                                 "
            + "  LEFT JOIN m_owner t14 ON t.out_owner_id = t14.id                                                                                       "
            + "  LEFT JOIN m_customer t15 ON t.out_consignor_id = t15.id                                                                                "
            + "  LEFT JOIN b_schedule_info t17 on t17.schedule_id = t.id                                                                                "
            + "  LEFT JOIN m_customer t18 on t17.customer_id = t18.id                                                                                                                   "
            + "  where true                                                                                                                                                             "
            + "    and t.is_delete = '" + DictConstant.DICT_B_IS_DELETE_FALSE +"'                                                                                                       "
            + "    and (t.status = #{p1.status,jdbcType=VARCHAR} or #{p1.status,jdbcType=VARCHAR} is null or #{p1.status,jdbcType=VARCHAR} ='')                                         "
            + "    and (t.code like concat('%',#{p1.code,jdbcType=VARCHAR},'%') or #{p1.code,jdbcType=VARCHAR} is null)                                                                 "
            + "    and (t17.waybill_contract_no like concat('%',#{p1.waybill_contract_no,jdbcType=VARCHAR},'%') or #{p1.waybill_contract_no,jdbcType=VARCHAR} is null)                  "
            + "    and (concat(ifnull(t7.contract_no,''),ifnull(t.contract_no,'')) like concat('%',#{p1.contract_no,jdbcType=VARCHAR},'%') or #{p1.contract_no,jdbcType=VARCHAR} is null)"
            + "    and (concat(t6.name,t6.pm,t6.code,t6.spec) like concat('%',#{p1.goods_name,jdbcType=VARCHAR},'%') or #{p1.goods_name,jdbcType=VARCHAR} is null)                      "
            + "    and (t.out_type = #{p1.out_type,jdbcType=VARCHAR} or #{p1.out_type,jdbcType=VARCHAR} is null)                                                                        "
            + "    and (t.in_type = #{p1.in_type,jdbcType=VARCHAR} or #{p1.in_type,jdbcType=VARCHAR} is null)                                                                           "
            + "    and (concat(t15.name,t15.short_name) like concat('%',#{p1.out_consignor_name,jdbcType=VARCHAR},'%') or #{p1.out_consignor_name,jdbcType=VARCHAR} is null)            "
            + "    and (concat(t14.name,t14.short_name) like concat('%',#{p1.out_owner_name,jdbcType=VARCHAR},'%') or #{p1.out_owner_name,jdbcType=VARCHAR} is null or #{p1.out_owner_name,jdbcType=VARCHAR} = '') "
            + "    and (concat(t13.name,t13.short_name) like concat('%',#{p1.in_consignor_name,jdbcType=VARCHAR},'%') or #{p1.in_consignor_name,jdbcType=VARCHAR} is null)              "
            + "    and (concat(t12.name,t12.short_name) like concat('%',#{p1.in_owner_name,jdbcType=VARCHAR},'%') or #{p1.in_owner_name,jdbcType=VARCHAR} is null or #{p1.in_owner_name,jdbcType=VARCHAR} = '') "
            + "    and (concat(t3.name,t3.short_name) like concat('%',#{p1.out_warehouse_name,jdbcType=VARCHAR},'%') or #{p1.out_warehouse_name,jdbcType=VARCHAR} is null)              "
            + "    and (concat(t4.name,t4.short_name) like concat('%',#{p1.in_warehouse_name,jdbcType=VARCHAR},'%') or #{p1.in_warehouse_name,jdbcType=VARCHAR} is null)                "
            + "    and (t.out_plan_code like concat('%',#{p1.out_plan_code,jdbcType=VARCHAR},'%') or #{p1.out_plan_code,jdbcType=VARCHAR} is null)                                      "
            + "    and (t.in_plan_code like concat('%',#{p1.in_plan_code,jdbcType=VARCHAR},'%') or #{p1.in_plan_code,jdbcType=VARCHAR} is null)                                         "
            + "    and (concat(t18.name, t18.short_name) like concat('%',#{p1.customer_name,jdbcType=VARCHAR},'%') or #{p1.customer_name,jdbcType=VARCHAR} is null or #{p1.customer_name,jdbcType=VARCHAR} = '')"
            + "    and (DATE_FORMAT(t.c_time, '%Y%m%d' ) &gt;= DATE_FORMAT(#{p1.start_time,jdbcType=DATE}, '%Y%m%d' ) or #{p1.start_time,jdbcType=DATE} is null)                         "
            + "    and (DATE_FORMAT(t.c_time, '%Y%m%d' ) &lt;= DATE_FORMAT(#{p1.over_time,jdbcType=DATE}, '%Y%m%d' ) or #{p1.over_time,jdbcType=DATE} is null)                           "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')                                                                "
            + "   <if test='p1.status_list != null and p1.status_list.length != 0' >                                                                                                     "
            + "    and t.status in                                                                                                                                                       "
            + "        <foreach collection='p1.status_list' item='item' index='index' open='(' separator=',' close=')'>                                                                  "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "   <if test='p1.out_warehouse_types != null and p1.out_warehouse_types.length != 0' >                                                                                         "
            + "    and t3.warehouse_type in                                                                                                                                              "
            + "        <foreach collection='p1.out_warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                          "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "   <if test='p1.in_warehouse_types != null and p1.in_warehouse_types.length != 0' >                                                                                          "
            + "    and t4.warehouse_type in                                                                                                                                              "
            + "        <foreach collection='p1.in_warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                           "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "
            + "    and (t.type = #{p1.type,jdbcType=VARCHAR} or #{p1.type,jdbcType=VARCHAR} is null or #{p1.type,jdbcType=VARCHAR} = '')                                                 "

            + "   <if test='p1.type_list != null and p1.type_list.length != 0' >                                                                                                         "
            + "    and t.type in                                                                                                                                                         "
            + "        <foreach collection='p1.type_list' item='item' index='index' open='(' separator=',' close=')'>                                                                    "
            + "         #{item}                                                                                                                                                          "
            + "        </foreach>                                                                                                                                                        "
            + "   </if>                                                                                                                                                                  "

            + "     ${p1.params.dataScopeAnnotation}                                                                                                                                    "
            + "</script>      ")
    int selectExportNum(@Param("p1") BScheduleVo searchCondition);

    @Select(""
            +  "  SELECT                                                                                                "
            +  "    t.id                                                                                                "
            +  "  FROM                                                                                                  "
            +  "  b_schedule t                                                                                          "
            +  "  LEFT JOIN b_order t1 ON t.order_id = t1.id                                                            "
            +  "  WHERE t1.serial_id = #{p1}                                                                            "
            +  "  AND t1.serial_type = #{p2}                                                                            "
            +  "  AND t.`status` != '"+DictConstant.DICT_B_SCHEDULE_STATUS_FIVE+"'                                      "
    )
    List<BScheduleVo> selectScheduleByOrderId(@Param("p1") Integer orderId,@Param("p2") String orderType);

    @Select(""
            + "  SELECT                                                                                                 "
            + "    id                                                                                                   "
            + "  FROM b_monitor                                                                                         "
            + "  WHERE schedule_id = #{p1}                                                                              "
            + "  AND status != '"+ DictConstant.DICT_B_MONITOR_STATUS_EIGHT +"'                                         "
    )
    List<Integer> selectMonitorByScheduleId(@Param("p1") Integer scheduleId);
}
